﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ExportQuotation : UserControl
    {
        private INISettings mySettings;
        private SqlConnection myConnection;
        public panel_ExportQuotation()
        {
            InitializeComponent();
            this.mySettings = new INISettings();
            this.myConnection = new SqlConnection(mySettings.SQLConnectionString);

            LoadData();
            this.ExportQuotation.PopupMenuShowing += ExportQuotation_PopupMenuShowing;
            this.exportQuotationContext.ItemClicked += exportQuotationContext_ItemClicked;
            this.ExportQuotation.KeyPress += ExportQuotation_KeyPress;
            this.ExportQuotation.DoubleClick += ExportQuotation_DoubleClick;
        }

        ///////////////////////////////////////////
        /// Call Edit Form
        //////////////////////////////////////////
        private void callEditForm()
        {
            
            DataRow row = ExportQuotation.GetFocusedDataRow();
            frm_Edit_ExportQuotation edit_form = new frm_Edit_ExportQuotation();
            edit_form.quotation_id = row["QuotationID"].ToString();
            if (edit_form.LoadData())
            {
                //edit_form.Show();
                edit_form.ShowDialog();
                LoadData();
            }
            else
            {
                edit_form.Dispose();
                MessageBox.Show("No Quotation to edit");
            } 
        }

        void ExportQuotation_DoubleClick(object sender, EventArgs e)
        {
            callEditForm();
        }

        void ExportQuotation_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                callEditForm();
            }
        }

        void exportQuotationContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.exportQuotationContext.Hide();
            DataRow row = ExportQuotation.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "editSelection":
                    callEditForm();
                    break;
                case "addSelection":
                    frm_Add_ExportQuotation add_Form = new frm_Add_ExportQuotation();
                    add_Form.ShowDialog();
                    LoadData();
                    break;
                case "uploadSelection":
                    UploadFile();
                    break;
                case "downloadSelection":
                    DownloadFile();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void ExportQuotation_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.exportQuotationContext.Show(MousePosition);            
        }

        ///////////////////////////////////////
        /// Load Data to Grid
        ///////////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();            
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.QuotationID, t2.FullName, t2.PIC, t1.Inputdate,"
                + " Staff1.StaffName, Staff2.StaffName, t1.ComOrPart,"
                + " t1.ExpiredDate, t1.BankInfor, t1.Note,"
                + " CASE WHEN t1.ExcelFile IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS ExcelFile"
                + " FROM [241_ExportQuotation] AS t1 LEFT OUTER JOIN"
                + " [222_Customer] AS t2 ON t2.CustomerID=t1.CustomerID LEFT OUTER JOIN"
                + " [222_Staff] AS Staff1 ON Staff1.StaffID=t1.RequestedBy LEFT OUTER JOIN"
                + " [222_Staff] AS Staff2 ON Staff2.StaffID=t1.MadeBy";
                //+ " WHERE t1.ExpiredDate > GETDATE()";
            sourceDataSet = mySQLManagement.SQL_Query(sql);            
            if (sourceDataSet != null)
            {
                gridExportQuotation.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Export Quotation");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("QuotationID", typeof(string));
                newTable.Columns.Add("Customer", typeof(string));
                newTable.Columns.Add("Contact", typeof(string));
                newTable.Columns.Add("InputDate", typeof(DateTime));
                newTable.Columns.Add("RequestedBy", typeof(string));
                newTable.Columns.Add("MadeBy", typeof(string));
                newTable.Columns.Add("ComOrPart", typeof(bool));
                newTable.Columns.Add("ExpiredDate", typeof(bool));                
                newTable.Columns.Add("BankInfor", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                newTable.Columns.Add("ExcelFile", typeof(bool));
                gridExportQuotation.DataSource = newTable;
            }
            ExportQuotation.Columns[0].Caption = "Quotation ID";
            ExportQuotation.Columns[1].Caption = "Customer";
            ExportQuotation.Columns[2].Caption = "Contact";
            ExportQuotation.Columns[3].Caption = "Input Date";
            ExportQuotation.Columns[4].Caption = "Requested By";
            ExportQuotation.Columns[5].Caption = "Made By";
            ExportQuotation.Columns[6].Caption = "Comp. Quotation";
            ExportQuotation.Columns[7].Caption = "Expired Date";
            ExportQuotation.Columns[8].Caption = "Bank Account";
            ExportQuotation.Columns[9].Caption = "Note";
            ExportQuotation.Columns[10].Caption = "Excel File";
            ExportQuotation.BestFitColumns();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_ExportQuotation add_Form = new frm_Add_ExportQuotation();            
            add_Form.ShowDialog();
            LoadData();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm();
        }

        ////////////////////////////////////////////////
        /// Upload file into Database
        ///////////////////////////////////////////////
        private void UploadFile()
        {
            DataRow row = ExportQuotation.GetFocusedDataRow();
            string selected_quotation = row["QuotationID"].ToString().Trim();

            OpenFileDialog ImportFile = new OpenFileDialog();
            ImportFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            ImportFile.FilterIndex = 1;
            ImportFile.Multiselect = false;
            DialogResult userClickOK = ImportFile.ShowDialog();

            if (userClickOK == DialogResult.OK)
            {
                byte[] file;                

                try
                {
                    using (var stream = new FileStream(ImportFile.FileName, FileMode.Open, FileAccess.Read))
                    {
                        using (var reader = new BinaryReader(stream))
                        {
                            file = reader.ReadBytes((int)stream.Length);
                        }
                    }

                    if (file.Length > 0)
                    {
                        myConnection.Open();
                        SqlCommand sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "UPDATE [241_ExportQuotation] SET ExcelFile=@File"
                            + " WHERE QuotationID='" + selected_quotation + "'";
                        sql_cmd.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
                        sql_cmd.ExecuteNonQuery();
                        sql_cmd.Dispose();
                        myConnection.Close();

                        MessageBox.Show("Upload File to Database successfully!");
                    }
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot Upload File to Database!");
                }                
            }
        }

        ///////////////////////////////////////////////////
        /// Download file From Database
        ///////////////////////////////////////////////////
        private void DownloadFile()
        {
            DataRow row = ExportQuotation.GetFocusedDataRow();
            string selected_quotation = row["QuotationID"].ToString().Trim();

            SaveFileDialog saveFDlg = new SaveFileDialog();
            saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
            saveFDlg.Title = "Save As";
            //saveFDlg.CheckFileExists = true;
            saveFDlg.FileName = selected_quotation + ".xlsx";
            if (saveFDlg.ShowDialog(this) == DialogResult.OK)
            {
                if (!string.IsNullOrEmpty(saveFDlg.FileName))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "SELECT [ExcelFile] FROM [241_ExportQuotation]"
                            + " WHERE QuotationID='" + selected_quotation + "'";
                        SqlDataReader dataReader = sql_cmd.ExecuteReader();
                        if (dataReader != null)
                        {
                            dataReader.Read();
                            var blob = new Byte[(dataReader.GetBytes(0, 0, null, 0, int.MaxValue))];
                            dataReader.GetBytes(0, 0, blob, 0, blob.Length);
                            using (var fs = new FileStream(saveFDlg.FileName, FileMode.Create, FileAccess.Write))
                                fs.Write(blob, 0, blob.Length);
                        }
                        dataReader.Close();
                        sql_cmd.Dispose();
                        myConnection.Close();
                        MessageBox.Show("Download File from Database successfully!");
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot Download File from Database!");
                    }                    
                }
            }
        }


        private void buttonUpload_Click(object sender, EventArgs e)
        {
            UploadFile();
        }

        private void buttonDownload_Click(object sender, EventArgs e)
        {
            DownloadFile();
        }
    }
}
